from typing import List

from flask import Blueprint
from openpyxl import Workbook
from sqlalchemy.orm import Query

from apps.a_common.constants import CREATE_USER_LOCK_KEY, SEX_MAP, SKILL_MAP
from apps.a_common.db import safe_commit
from apps.a_common.lock import lock_by_key
from apps.a_common.log import MyLogger
from apps.foundation import db, cache
from apps.projects.models import ProjectUser
from apps.users.exceptions import RepeatedUsername
from apps.users.models import User, Permission, PermissionGroup
from utils.storage import get_key_from_url

user_blueprint = Blueprint('user', __name__)
log = MyLogger('user view')


def create_user(form):
    with lock_by_key(CREATE_USER_LOCK_KEY.format(form['username'])) as lock:
        if not lock:
            raise RepeatedUsername()
        if User.query.filter(User.username == form['username']).first():
            raise RepeatedUsername()
        user = User(username=form['username'])
        user.set_password(form['password'])
        db.session.add(user)
        safe_commit()
        return user


@cache.memoize(10)
def get_permissions_by_user_id(user_id: int) -> List['Permission']:
    permission_ids = set()
    [permission_ids.update(i[0]) for i in
     PermissionGroup.query.filter(PermissionGroup.user_id_list.any(user_id)).with_entities(
         PermissionGroup.permission_id_list) or []]
    permissions = Permission.query.filter(Permission.id.in_(permission_ids)).all()
    return permissions


def get_user_by_id(user_id):
    return User.query.filter(User.id == user_id).first()


def set_user_info_by_form(user: User, form: dict):
    for key, value in form.items():
        log.info(f'{key} {value}')
        if key == 'password' and value:
            user.set_password(value)
        elif key == 'avatar' and value:
            user.avatar_store_id = get_key_from_url(value)
        else:
            setattr(user, key, value)
    return user


def search_user_by_form(form) -> Query:
    queryset = User.query
    if form.get('skills'):
        queryset = queryset.filter(*[User.skills.any(int(i)) for i in form.get('skills')])
    if form.get('project_id'):
        queryset = queryset.join(ProjectUser, ProjectUser.user_id == User.id).filter(
            ProjectUser.project_id == form['project_id'])
    return queryset


def generate_user_excel(users):
    wb = Workbook()
    ws = wb.create_sheet('用户清单', 0)
    ws.append(('姓名', '性别', '技能', '工作经历'))
    for user in users:
        row = [user.full_name, SEX_MAP.get(user.sex, ''), '，'.join([SKILL_MAP.get(i, '') for i in user.skills or []]),
               user.work_experience]
        ws.append(row)
    return wb
